/**
 * 
 */
package cn.telling.productapprove.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;


import cn.telling.common.enums.users.UsersCategory;
import cn.telling.common.pager.PageVo;
import cn.telling.common.pager.Pager;
import cn.telling.common.util.DateTimeTool;
import cn.telling.common.util.TellingConstance;
import cn.telling.common.vo.LoginingVo;
import cn.telling.productapprove.dao.IProductApproveDao;
import cn.telling.productapprove.vo.ConditionApproveVo;
import cn.telling.productapprove.vo.ProductApproveVo;
import cn.telling.productapprove.vo.ProductBidApproveVo;
import cn.telling.productapprove.vo.ShopProductVO;
import cn.telling.utils.AutoInjection;
import cn.telling.utils.BaseUtil;
import cn.telling.utils.StringHelperTools;

/**
 * @Title: ProductApproveDaoImpl.java
 * @Package com.productapprove.dao.impl
 * @Description: 商品审批处理数据访问接口实现
 * @author guohui
 * @date 2013-4-26 上午9:14:45
 * @version V1.0
 */
@Repository("ProductApproveDao")
public class ProductApproveDaoImpl implements IProductApproveDao {

    private static Logger logger = Logger.getLogger(ProductApproveDaoImpl.class);

    @Autowired
    @Qualifier("film-template")
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /*@Autowired
    @Qualifier("AreaDao")
    private IAreaInfoDao areaDao;*/

    @Override
    public List<ProductApproveVo> GetByPage(ConditionApproveVo condition, PageVo pageVo, LoginingVo loVo) {
        String sql = " SELECT " + " SPA.ID " + ",SPA.SUPPLYAREAID " + ",PM.PRODUCTNAME " + ",SA0.AREAID "
                + ",AI.AREANAME " + ",SPA.PRICEBEFORE " + ",SPA.PRICEAFTER " + ",SPA.STOCKBEFORE " + ",SPA.STOCKAFTER "
                + ",SPA.REMARK " + ",SPA.EXPAPPROVETIME " + ",SPA.APPROVETIME " + ",UR.USERNAME " + ",SPT.SUPPLYID "
                + ",SPL.NAME AS SUPPLYNAME " + ",SPA.APPROVESTATUS " + ",SPA.SALESTATUS " + ",SA0.ISCONTINUOUS "
                + ",SA0.STATUS " + ",SA0.SALETYPE " + ",SA0.SUPPLYPRODUCTID AS SHOPPRODUCTID "
                + ",NVL(BI.COMPANYNAME, BI.SHOPNAME) AS COMPANYNAME " + " FROM " + "  SHOP_PRODUCT_APPROVE SPA "
                + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPA.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN SHOP_PRODUCT SPT "
                + "  ON SA0.SUPPLYPRODUCTID = SPT.ID AND SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " LEFT JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " LEFT JOIN BUYERINFO BI "
                + "  ON SA0.CUSTOMID = BI.BUYERID AND BI.DELFLAG <> '0' " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' " + " LEFT JOIN USERS UR "
                + "  ON SPA.APPROVEUSER = UR.USERID " + " WHERE SPA.DEL_FLAG = '1' AND SA0.SALETYPE <> '1' ";

        // 超级管理员放开区域限制
        if (!UsersCategory.Administrator.equals(UsersCategory.valueOf(Integer.parseInt(loVo.getCategoryId())))) {
            // 准备优化的
            sql += " AND EXISTS (SELECT RSF.AREAID FROM " + " USERSSTAFFRELATION US "
                    + " INNER JOIN USERSTAFFINFO UF ON US.STAFFID = UF.STAFFID"
                    + " INNER JOIN RELATION_STAFFMANAGEAREA RSF ON UF.STAFFID = RSF.STAFFID" + " WHERE US.USERID='"
                    + loVo.getLogin_id() + "' AND nvl(AI.Areaid, BI.areaid)=RSF.AREAID) ";

        }

        if (condition.getSupplyId() != null && !"".equalsIgnoreCase(condition.getSupplyId())) {
            if ("tyAll".equalsIgnoreCase(condition.getSupplyId())) {
                sql += " AND SPL.SUPPLY_TYPE = '01' ";
            } else {
                sql += " AND SPT.SUPPLYID=" + condition.getSupplyId() + "";
            }
        } else {
            sql += " AND (SPL.SUPPLY_TYPE <> '01' OR SPL.SUPPLY_TYPE IS NULL) ";
        }
        if (condition.getApproveStatus() != null && !"".equalsIgnoreCase(condition.getApproveStatus())) {
            if ("1".equals(condition.getApproveStatus())) {
                // 未审核
                sql += " AND SPA.APPROVESTATUS='1' ";
            } else {
                if ("2".equals(condition.getApproveStatus())) {
                    // 二审中
                    sql += " AND (SPA.APPROVESTATUS='4') ";
                } else if ("3".equals(condition.getApproveStatus())) {
                    // 已审核
                    sql += " AND (SPA.APPROVESTATUS='3') ";
                }
            }
        }
        if (condition.getProductName() != null && !"".equalsIgnoreCase(condition.getProductName())) {
            sql += " AND lower(replace(PM.PRODUCTNAME,' ','') ) like '%"
                    + condition.getProductName().replaceAll("\\s*", "").toLowerCase() + "%'";
        }

        sql += " ORDER BY SPA.EXPAPPROVETIME DESC,SPT.SUPPLYID,SPT.PRODUCTID ";

        String execSql = "";
        execSql = Pager.getPageDatas(pageVo, sql);

        return getJdbcTemplate().query(execSql, new ProductApproveVoMapper());
    }

    private static final class ProductApproveVoMapper implements RowMapper<ProductApproveVo> {

        public ProductApproveVo mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductApproveVo productApproveVo = new ProductApproveVo();
            productApproveVo.setId(rs.getString("ID"));
            productApproveVo.setShopProductId(rs.getString("SHOPPRODUCTID"));
            productApproveVo.setProductName(rs.getString("PRODUCTNAME"));
            productApproveVo.setAreaId(rs.getString("AREAID"));
            productApproveVo.setAreaName(rs.getString("AREANAME"));
            productApproveVo.setPriceBefore(rs.getString("PRICEBEFORE"));
            productApproveVo.setPriceAfter(rs.getString("PRICEAFTER"));
            productApproveVo.setStockBefore(rs.getString("STOCKBEFORE"));
            productApproveVo.setStockAfter(rs.getString("STOCKAFTER"));
            productApproveVo.setRemark(rs.getString("REMARK"));
            productApproveVo.setExpApproveDate(DateTimeTool.getDateString(rs.getDate("EXPAPPROVETIME")));
            Date approveDate = rs.getDate("APPROVETIME");
            if (approveDate != null) {
                productApproveVo.setApproveDate(DateTimeTool.getDateString(approveDate));
            }
            productApproveVo.setApproveUser(rs.getString("USERNAME"));
            productApproveVo.setSupplyId(rs.getString("SUPPLYID"));
            productApproveVo.setSupplyName(rs.getString("SUPPLYNAME"));
            productApproveVo.setApproveStatus(rs.getString("APPROVESTATUS"));
            productApproveVo.setSaleStatus(rs.getString("SALESTATUS"));
            productApproveVo.setContinuous(rs.getString("ISCONTINUOUS"));
            productApproveVo.setStatus(rs.getString("STATUS"));
            productApproveVo.setSaleType(rs.getString("SALETYPE"));
            productApproveVo.setSupplyAreaId(rs.getString("SUPPLYAREAID"));
            productApproveVo.setCustName(rs.getString("COMPANYNAME"));
            productApproveVo.setSupplyType(rs.getString("supply_type"));
            return productApproveVo;
        }
    }

    private static final class ProductApproveVoMapper2 implements RowMapper<ProductApproveVo> {

        public ProductApproveVo mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductApproveVo productApproveVo = new ProductApproveVo();
            productApproveVo.setId(rs.getString("ID"));
            productApproveVo.setShopProductId(rs.getString("SHOPPRODUCTID"));
            productApproveVo.setProductName(rs.getString("PRODUCTNAME"));
            productApproveVo.setAreaId(rs.getString("AREAID"));
            productApproveVo.setAreaName(rs.getString("AREANAME"));
            productApproveVo.setPriceBefore(rs.getString("PRICEBEFORE"));
            productApproveVo.setPriceAfter(rs.getString("PRICEAFTER"));
            productApproveVo.setStockBefore(rs.getString("STOCKBEFORE"));
            productApproveVo.setStockAfter(rs.getString("STOCKAFTER"));
            productApproveVo.setRemark(rs.getString("REMARK"));
            productApproveVo.setExpApproveDate(DateTimeTool.getDateString(rs.getDate("EXPAPPROVETIME")));
            Date approveDate = rs.getDate("APPROVETIME");
            if (approveDate != null) {
                productApproveVo.setApproveDate(DateTimeTool.getDateString(approveDate));
            }
            productApproveVo.setApproveUser(rs.getString("USERNAME"));
            productApproveVo.setSupplyId(rs.getString("SUPPLYID"));
            productApproveVo.setSupplyName(rs.getString("SUPPLYNAME"));
            productApproveVo.setApproveStatus(rs.getString("APPROVESTATUS"));
            productApproveVo.setSaleStatus(rs.getString("SALESTATUS"));
            productApproveVo.setContinuous(rs.getString("ISCONTINUOUS"));
            productApproveVo.setStatus(rs.getString("STATUS"));
            productApproveVo.setSaleType(rs.getString("SALETYPE"));
            productApproveVo.setSupplyAreaId(rs.getString("SUPPLYAREAID"));
            productApproveVo.setCustId(rs.getString("CUSTOMID"));
            productApproveVo.setCustName(rs.getString("COMPANYNAME"));
            productApproveVo.setSupplyType(rs.getString("supply_type"));
            return productApproveVo;
        }
    }

    @Override
    public int GetTotalCount(ConditionApproveVo condition, LoginingVo loVo) {
        String sql = "SELECT COUNT(1) " + " FROM " + "  SHOP_PRODUCT_APPROVE SPA " + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPA.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN SHOP_PRODUCT SPT "
                + "  ON SA0.SUPPLYPRODUCTID = SPT.ID AND SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " LEFT JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " LEFT JOIN BUYERINFO BI "
                + "  ON SA0.CUSTOMID = BI.BUYERID AND BI.DELFLAG <> '0' " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' " + " LEFT JOIN USERS UR "
                + "  ON SPA.APPROVEUSER = UR.USERID " + " WHERE SPA.DEL_FLAG = '1' AND SA0.SALETYPE <> '1' ";
        // 超级管理员放开区域限制
        if (!UsersCategory.Administrator.equals(UsersCategory.valueOf(Integer.parseInt(loVo.getCategoryId())))) {

            // 准备优化
            // sql +=" AND EXISTS (SELECT RSF.AREAID FROM " +
            // " USERSSTAFFRELATION US " +
            // " INNER JOIN USERSTAFFINFO UF ON US.STAFFID = UF.STAFFID" +
            // " INNER JOIN RELATION_STAFFMANAGEAREA RSF ON UF.STAFFID = RSF.STAFFID"
            // +
            // " WHERE US.USERID='"+loVo.getLogin_id()+"' AND nvl(AI.Areaid, BI.areaid)=RSF.AREAID) ";

            List<BigDecimal> areaLs = loVo.getAreaLs();
            if (areaLs != null && areaLs.size() > 0) {
                // 取得用户的管理区域ID，排除全国的2
                String area_id = "";
                /*if (areaLs.get(0).intValue() != 2) {
                    area_id = StringHelperTools.nvl(areaDao.GetAreaByIdAndLevel(areaLs.get(0), 2));
                } else {
                    area_id = StringHelperTools.nvl(areaDao.GetAreaByIdAndLevel(areaLs.get(1), 2));
                }*/
                sql += " and "
                        + area_id
                        + " in(select ai1.areaid from areainfo ai1 start with ai1.areaid = nvl(AI.Areaid,BI.areaid) connect by prior ai1.parentid=ai1.areaid)";
            } else {
                sql += " and 1 = 2 ";
            }
        }
        if (condition.getSupplyId() != null && !"".equalsIgnoreCase(condition.getSupplyId())) {
            if ("tyAll".equalsIgnoreCase(condition.getSupplyId())) {
                sql += " AND SPL.SUPPLY_TYPE = '01' ";
            } else {
                sql += " AND SPT.SUPPLYID=" + condition.getSupplyId() + "";
            }
        } else {
            sql += " AND (SPL.SUPPLY_TYPE <> '01' OR SPL.SUPPLY_TYPE IS NULL) ";
        }
        if (condition.getApproveStatus() != null && !"".equalsIgnoreCase(condition.getApproveStatus())) {
            if ("1".equals(condition.getApproveStatus())) {
                // 未审核
                sql += " AND SPA.APPROVESTATUS='1' ";

                // yuanquan
            } else {
                if ("2".equals(condition.getApproveStatus())) {
                    // 二审中
                    sql += " AND (SPA.APPROVESTATUS='4') ";
                } else if ("3".equals(condition.getApproveStatus())) {
                    // 已审核
                    sql += " AND (SPA.APPROVESTATUS='3') ";
                }
            }
        }
        if (condition.getProductName() != null && !"".equalsIgnoreCase(condition.getProductName())) {
            sql += " AND lower(replace(PM.PRODUCTNAME,' ','') ) like '%"
                    + condition.getProductName().replaceAll("\\s*", "").toLowerCase() + "%'";
        }
        return getJdbcTemplate().queryForInt(sql);
    }

    @Override
    public ProductApproveVo GetApproveInfoById(String approveId) {
        String sql = " SELECT " + " SPA.ID " + ",SPA.SUPPLYAREAID " + ",PM.PRODUCTNAME " + ",SA0.AREAID "
                + ",SA0.CUSTOMID " + ",AI.AREANAME " + ",SPA.PRICEBEFORE " + ",SPA.PRICEAFTER " + ",SPA.STOCKBEFORE "
                + ",SPA.STOCKAFTER " + ",SPA.REMARK " + ",SPA.EXPAPPROVETIME " + ",SPA.APPROVETIME " + ",UR.USERNAME "
                + ",SPT.SUPPLYID " + ",SPL.NAME AS SUPPLYNAME " + ",SPA.APPROVESTATUS " + ",SPA.SALESTATUS "
                + ",SA0.ISCONTINUOUS " + ",SA0.STATUS " + ",SA0.SALETYPE " + ",SA0.SUPPLYPRODUCTID AS SHOPPRODUCTID "
                + ",NVL(BI.COMPANYNAME, BI.SHOPNAME) AS COMPANYNAME " + ",SPL.supply_type" + " FROM "
                + "  SHOP_PRODUCT_APPROVE SPA " + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPA.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN SHOP_PRODUCT SPT "
                + "  ON SA0.SUPPLYPRODUCTID = SPT.ID AND SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " LEFT JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " LEFT JOIN BUYERINFO BI "
                + "  ON SA0.CUSTOMID = BI.BUYERID AND BI.DELFLAG <> '0' " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' " + " LEFT JOIN USERS UR "
                + "  ON SPA.APPROVEUSER = UR.USERID " + " WHERE SPA.DEL_FLAG = '1' AND SPA.ID = " + approveId;

        List<ProductApproveVo> lst = getJdbcTemplate().query(sql, new ProductApproveVoMapper2());
        if (lst != null && lst.size() > 0) {
            return lst.get(0);
        } else {
            return null;
        }
    }

    @Override
    public ProductApproveVo GetApproveInfoBySupplyAreaId(String supplyAreaId) {
        String sql = " SELECT " + " SPA.ID " + ",SPA.SUPPLYAREAID " + ",PM.PRODUCTNAME " + ",SA0.AREAID "
                + ",AI.AREANAME " + ",SPA.PRICEBEFORE " + ",SPA.PRICEAFTER " + ",SPA.STOCKBEFORE " + ",SPA.STOCKAFTER "
                + ",SPA.REMARK " + ",SPA.EXPAPPROVETIME " + ",SPA.APPROVETIME " + ",UR.USERNAME " + ",SPT.SUPPLYID "
                + ",SPL.NAME AS SUPPLYNAME " + ",SPA.APPROVESTATUS " + ",SPA.SALESTATUS " + ",SA0.ISCONTINUOUS "
                + ",SA0.STATUS " + ",SA0.SALETYPE " + ",SA0.SUPPLYPRODUCTID AS SHOPPRODUCTID "
                + ",NVL(BI.COMPANYNAME, BI.SHOPNAME) AS COMPANYNAME " + " FROM " + "  SHOP_PRODUCT_APPROVE SPA "
                + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPA.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN SHOP_PRODUCT SPT "
                + "  ON SA0.SUPPLYPRODUCTID = SPT.ID AND SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " LEFT JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " LEFT JOIN BUYERINFO BI "
                + "  ON SA0.CUSTOMID = BI.BUYERID AND BI.DELFLAG <> '0' " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' " + " LEFT JOIN USERS UR "
                + "  ON SPA.APPROVEUSER = UR.USERID " + " WHERE SPA.DEL_FLAG = '1' AND SPA.SUPPLYAREAID = "
                + supplyAreaId + " ORDER BY SPA.EXPAPPROVETIME DESC ";

        List<ProductApproveVo> lst = getJdbcTemplate().query(sql, new ProductApproveVoMapper());
        if (lst != null && lst.size() > 0) {
            return lst.get(0);
        } else {
            return null;
        }
    }

    @Override
    public int UpdateSupplyArea(ProductApproveVo productApproveVo) {
        List<String> params = new ArrayList<String>();
        StringBuffer sbSql = new StringBuffer(" UPDATE SUPPLY_AREA0 SET ");
        sbSql.append(" SALESTATUS = ? ");
        params.add(productApproveVo.getApproveStatus());
        if (productApproveVo.getContinuous() != null) {
            sbSql.append(" ,ISCONTINUOUS = ? ");
            params.add(productApproveVo.getContinuous());
        }
        if (productApproveVo.getPriceAfter() != null) {
            sbSql.append(" ,PRICERETAILONLINE = ? ");
            params.add(productApproveVo.getPriceAfter());
        }
        if (productApproveVo.getStockAfter() != null) {
            sbSql.append(" ,STOCKNUMBER = STOCKNUMBER + ? ");
            sbSql.append(" ,OVERPLUSNUMBER = OVERPLUSNUMBER + ? ");
            params.add(productApproveVo.getStockAfter());
            params.add(productApproveVo.getStockAfter());
        }
        if (productApproveVo.getStatus() != null) {
            sbSql.append(" ,STATUS = ? ");
            params.add(productApproveVo.getStatus());
            if ("1".equals(productApproveVo.getStatus())) {
                // 上架
                sbSql.append(" ,ONSHELFTIME = SYSDATE ");
            } else if ("2".equals(productApproveVo.getStatus())) {
                // 下架
                sbSql.append(" ,OFFSHELFTIME = SYSDATE ");
            }
        }
        sbSql.append(" WHERE ID = ? ");
        params.add(productApproveVo.getSupplyAreaId());
        sbSql.append(" AND DEL_FLAG = '1' AND ISVALID = '01' ");

        if (productApproveVo.getStockAfter() != null) {
            sbSql.append(" and (OVERPLUSNUMBER + ?) >= 0 ");
            params.add(productApproveVo.getStockAfter());
        }

        return getJdbcTemplate().update(sbSql.toString(), params.toArray());
    }

    @Override
    public int UpdateApprove(ProductApproveVo productApproveVo) {
        List<String> params = new ArrayList<String>();
        StringBuffer sbSql = new StringBuffer(" UPDATE SHOP_PRODUCT_APPROVE SET ");
        sbSql.append(" APPROVESTATUS = ? ");
        params.add(productApproveVo.getApproveStatus());
        sbSql.append(" ,APPROVETIME = SYSDATE ");
        sbSql.append(" ,APPROVEUSER = ? ");
        params.add(productApproveVo.getApproveUser());
        sbSql.append(" ,UPDATE_DATE = SYSDATE ");
        sbSql.append(" ,UPDATE_USER = ? ");
        params.add(productApproveVo.getApproveUser());
        sbSql.append(" WHERE ID = ? ");
        params.add(productApproveVo.getId());
        logger.debug("审核通过的sql:" + BaseUtil.logSQL(sbSql.toString(), params.toArray()));
        return getJdbcTemplate().update(sbSql.toString(), params.toArray());
    }

    /*@Override
    public List<SupplyVo> getSupplyName() {

        String sql = "SELECT ID,USER_ID,NAME FROM SUPPLY WHERE DEL_FLAG='1' AND (SUPPLY_TYPE NOT IN ('01','02','03') OR SUPPLY_TYPE IS NULL)   AND NAME IS NOT NULL ORDER BY ID";
        List<SupplyVo> list = getJdbcTemplate().query(sql, new SupplyVoMapper());
        return list;
    }*/

    /*private static final class SupplyVoMapper implements RowMapper<SupplyVo> {

        public SupplyVo mapRow(ResultSet rs, int rowNum) throws SQLException {
            SupplyVo supplyVo = new SupplyVo();
            supplyVo.setId(rs.getBigDecimal("ID"));
            supplyVo.setUser_id(rs.getBigDecimal("USER_ID"));
            supplyVo.setName(rs.getString("NAME"));
            return supplyVo;
        }
    }*/

    /*@Override
    public List<SupplyVo> getTySupplyName() {

        String sql = "SELECT ID,USER_ID,NAME FROM SUPPLY WHERE DEL_FLAG='1' AND SUPPLY_TYPE = '01' ORDER BY ID";
        List<SupplyVo> list = getJdbcTemplate().query(sql, new SupplyVoMapper());
        return list;
    }*/

    @Override
    public int GetBidTotalCount(ConditionApproveVo condition) {
        String sql = "SELECT COUNT(1) " + " FROM " + "  SHOP_PRODUCT SPT " + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPT.ID = SA0.SUPPLYPRODUCTID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " INNER JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' "
                + " WHERE SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " AND SA0.SALETYPE = '1' AND TO_CHAR(SA0.CREATETIME, 'YYYYMMDD') = TO_CHAR(SYSDATE - 1, 'YYYYMMDD') ";
        if (condition.getAreaId2() != null && !"".equalsIgnoreCase(condition.getAreaId2())) {
            sql += " AND AI.AREAID=" + condition.getAreaId2() + "";
        } else if (condition.getAreaId1() != null && !"".equalsIgnoreCase(condition.getAreaId1())) {
            sql += " AND AI.PARENTID=" + condition.getAreaId1() + "";
        }
        if (condition.getProductName() != null && !"".equalsIgnoreCase(condition.getProductName())) {
            sql += " AND lower(replace(PM.PRODUCTNAME,' ','') ) like '%"
                    + condition.getProductName().replaceAll("\\s*", "").toLowerCase() + "%'";
        }
        sql += " GROUP BY SPT.PRODUCTID,SA0.AREAID ";
        List<Object> lst = getJdbcTemplate().queryForList(sql, Object.class);
        return lst.size();
    }

    @Override
    public List<ProductBidApproveVo> GetBidByPage(ConditionApproveVo condition, PageVo pageVo) {
        String sql = "SELECT SPT.PRODUCTID " + ",PM.PRODUCTNAME " + ",SA0.AREAID " + ",AI.AREANAME "
                + ",MIN(SA0.SALESTATUS) AS APPROVESTATUS " + ",SUM(1) AS BIDCOUNT " + " FROM " + "  SHOP_PRODUCT SPT "
                + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPT.ID = SA0.SUPPLYPRODUCTID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " INNER JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' "
                + " WHERE SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " AND SA0.SALETYPE = '1' AND TO_CHAR(SA0.CREATETIME, 'YYYYMMDD') = TO_CHAR(SYSDATE - 1, 'YYYYMMDD') ";
        if (condition.getAreaId2() != null && !"".equalsIgnoreCase(condition.getAreaId2())) {
            sql += " AND AI.AREAID=" + condition.getAreaId2() + "";
        } else if (condition.getAreaId1() != null && !"".equalsIgnoreCase(condition.getAreaId1())) {
            sql += " AND AI.PARENTID=" + condition.getAreaId1() + "";
        }
        if (condition.getProductName() != null && !"".equalsIgnoreCase(condition.getProductName())) {
            sql += " AND lower(replace(PM.PRODUCTNAME,' ','') ) like '%"
                    + condition.getProductName().replaceAll("\\s*", "").toLowerCase() + "%'";
        }
        sql += " GROUP BY SPT.PRODUCTID,PM.PRODUCTNAME,SA0.AREAID,AI.AREANAME ORDER BY SPT.PRODUCTID ";

        String execSql = "";
        execSql = Pager.getPageDatas(pageVo, sql);

        return getJdbcTemplate().query(execSql, new ProductBidApproveVoMapper());
    }

    private static final class ProductBidApproveVoMapper implements RowMapper<ProductBidApproveVo> {

        public ProductBidApproveVo mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductBidApproveVo productBidApproveVo = new ProductBidApproveVo();
            productBidApproveVo.setProductId(rs.getString("PRODUCTID"));
            productBidApproveVo.setProductName(rs.getString("PRODUCTNAME"));
            productBidApproveVo.setAreaId(rs.getString("AREAID"));
            productBidApproveVo.setAreaName(rs.getString("AREANAME"));
            String approveStatus = rs.getString("APPROVESTATUS");
            if ("1".equals(approveStatus)) {
                productBidApproveVo.setApproveStatus("1");
            } else if ("3".equals(approveStatus)) {
                productBidApproveVo.setApproveStatus("3");
            } else if ("4".equals(approveStatus)) {
                productBidApproveVo.setApproveStatus("4");
            } else {
                productBidApproveVo.setApproveStatus("2");
            }
            productBidApproveVo.setBidCount(rs.getString("BIDCOUNT"));
            return productBidApproveVo;
        }
    }

    @Override
    public List<ProductApproveVo> GetBidApproveInfo(String productId, String areaId) {
        String sql = " SELECT " + " '' AS ID " + ",SA0.ID AS SUPPLYAREAID " + ",'' AS PRODUCTNAME " + ",SA0.AREAID "
                + ",AI.AREANAME " + ",'' AS PRICEBEFORE " + ",SA0.PRICERETAILONLINE AS PRICEAFTER "
                + ",'' AS STOCKBEFORE " + ",SA0.STOCKNUMBER AS STOCKAFTER " + ",'' AS REMARK "
                + ",SYSDATE AS EXPAPPROVETIME " + ",SYSDATE AS APPROVETIME " + ",'' AS USERNAME " + ",SPT.SUPPLYID "
                + ",SPL.NAME AS SUPPLYNAME " + ",SA0.SALESTATUS AS APPROVESTATUS " + ",'' AS SALESTATUS "
                + ",SA0.ISCONTINUOUS " + ",SA0.STATUS " + ",SA0.SALETYPE " + ",SA0.SUPPLYPRODUCTID AS SHOPPRODUCTID "
                + ",'' AS COMPANYNAME " + " FROM " + "  SHOP_PRODUCT SPT " + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPT.ID = SA0.SUPPLYPRODUCTID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " INNER JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' "
                + " WHERE SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " AND SA0.SALETYPE = '1' AND TO_CHAR(SA0.CREATETIME, 'YYYYMMDD') = TO_CHAR(SYSDATE - 1, 'YYYYMMDD') "
                + " AND SPT.PRODUCTID = " + productId + " AND SA0.AREAID = " + areaId + ""
                + " ORDER BY SA0.PRICERETAILONLINE,SA0.STOCKNUMBER DESC ";

        List<ProductApproveVo> lst = getJdbcTemplate().query(sql, new ProductApproveVoMapper());
        return lst;
    }

    @Override
    public int UpdateBidApproveSucc(ProductApproveVo productApproveVo) {
        List<String> params = new ArrayList<String>();
        StringBuffer sbSql = new StringBuffer(" UPDATE SUPPLY_AREA0 SET ");
        sbSql.append(" SALESTATUS = 3, STATUS = 1, ONSHELFTIME = SYSDATE ");
        sbSql.append(" WHERE ID = ? ");
        params.add(productApproveVo.getSupplyAreaId());
        sbSql.append(" AND DEL_FLAG = '1' AND ISVALID = '01' ");

        return getJdbcTemplate().update(sbSql.toString(), params.toArray());
    }

    @Override
    public int UpdateBidApproveFail(ProductApproveVo productApproveVo) {
        List<String> params = new ArrayList<String>();
        StringBuffer sbSql = new StringBuffer(" UPDATE SUPPLY_AREA0 SET ");
        sbSql.append(" SALESTATUS = 2, STATUS = 2, OFFSHELFTIME = SYSDATE ");
        sbSql.append(" WHERE ID = ? ");
        params.add(productApproveVo.getSupplyAreaId());
        sbSql.append(" AND DEL_FLAG = '1' AND ISVALID = '01' ");

        return getJdbcTemplate().update(sbSql.toString(), params.toArray());
    }

    @Override
    public int GetTotalCount(ConditionApproveVo condition, LoginingVo loVo, String Type) {
        String sql = "SELECT COUNT(1) " + " FROM " + "  SHOP_PRODUCT_APPROVE SPA " + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPA.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN SHOP_PRODUCT SPT "
                + "  ON SA0.SUPPLYPRODUCTID = SPT.ID AND SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " LEFT JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " LEFT JOIN BUYERINFO BI "
                + "  ON SA0.CUSTOMID = BI.BUYERID AND BI.DELFLAG <> '0' " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' " + " LEFT JOIN USERS UR "
                + "  ON SPA.APPROVEUSER = UR.USERID " +

                " REPLACEHERE " + // 用于替换

                " WHERE SPA.DEL_FLAG = '1' AND SA0.SALETYPE <> '1' ";
        // 产品审核(天音定向)的场合
        if (Type.equals("01")) {
            sql = sql.replaceAll("REPLACEHERE", ",ncproductmain np,relation_productnc rp");
            sql += " and np.invcode = rp.invcode and rp.productid = PM.productid  ";

            sql += " and SA0.SALETYPE  in (3,4)";

        }// 产品审核(天音非定向)
        else if (Type.equals("")) {
            sql = sql.replaceAll("REPLACEHERE", ",ncproductmain np,relation_productnc rp");
            sql += " and np.invcode = rp.invcode and rp.productid = PM.productid  ";

            sql += " and SA0.SALETYPE  in (2)";

            // 产品审核(供应商)的场合
        } else {
            sql = sql.replaceAll("REPLACEHERE", "");
        }
        // 超级管理员放开区域限制
        if (!UsersCategory.Administrator.equals(UsersCategory.valueOf(Integer.parseInt(loVo.getCategoryId())))) {
            sql += " AND EXISTS (SELECT RSF.AREAID FROM " + " USERSSTAFFRELATION US "
                    + " INNER JOIN USERSTAFFINFO UF ON US.STAFFID = UF.STAFFID"
                    + " INNER JOIN RELATION_STAFFMANAGEAREA RSF ON UF.STAFFID = RSF.STAFFID" + " WHERE US.USERID='"
                    + loVo.getLogin_id() + "' AND nvl(AI.Areaid, BI.areaid)=RSF.AREAID) ";
        }
        if (condition.getSupplyId() != null && !"".equalsIgnoreCase(condition.getSupplyId())) {
            if ("tyAll".equalsIgnoreCase(condition.getSupplyId())) {
                sql += " AND SPL.SUPPLY_TYPE = '01' ";
            } else {
                sql += " AND SPT.SUPPLYID=" + condition.getSupplyId() + "";
            }
        } else {
            // （排除天音产品，厂家自动，厂家手动）
            sql += " AND (SPL.SUPPLY_TYPE NOT IN ('01','02','03') OR SPL.SUPPLY_TYPE IS NULL) ";
        }
        if (!"".equals(StringHelperTools.nvl(condition.getApproveStatus()))) {
            // 未审核的场合
            if ("1".equals(condition.getApproveStatus())) {
                // 未审核
                sql += " AND SPA.APPROVESTATUS='1' ";
            }// 审核的场合
            else {
                if ("2".equals(condition.getApproveStatus())) {
                    // 二审中
                    sql += " AND (SPA.APPROVESTATUS='4') ";
                } else if ("3".equals(condition.getApproveStatus())) {
                    // 已审核
                    sql += " AND (SPA.APPROVESTATUS='3' OR SPA.APPROVESTATUS='2') ";
                }
            }
        }
        if (!"".equals(StringHelperTools.nvl(condition.getProductName()))) {
            sql += " AND lower(replace(PM.PRODUCTNAME,' ','') ) like '%"
                    + condition.getProductName().replaceAll("\\s*", "").toLowerCase() + "%'";
        }
        return getJdbcTemplate().queryForInt(sql);
    }

    @Override
    public List<ProductApproveVo> GetByPage(ConditionApproveVo condition, PageVo pageVo, LoginingVo loVo, String Type) {
        String sql = " SELECT " + " SPA.ID " + ",SPA.SUPPLYAREAID " + ",PM.PRODUCTNAME " + ",SA0.AREAID "
                + ",AI.AREANAME " + ",SPA.PRICEBEFORE " + ",SPA.PRICEAFTER " + ",SPA.STOCKBEFORE " + ",SPA.STOCKAFTER "
                + ",SPA.REMARK " + ",SPA.EXPAPPROVETIME " + ",SPA.APPROVETIME " + ",UR.USERNAME " + ",SPT.SUPPLYID "
                + ",SPL.NAME AS SUPPLYNAME " + ",SPA.APPROVESTATUS " + ",SPA.SALESTATUS " + ",SA0.ISCONTINUOUS "
                + ",SA0.STATUS " + ",SA0.SALETYPE " + ",SA0.SUPPLYPRODUCTID AS SHOPPRODUCTID "
                + ",NVL(BI.COMPANYNAME, BI.SHOPNAME) AS COMPANYNAME " + ",SPL.supply_type" + " FROM "
                + "  SHOP_PRODUCT_APPROVE SPA " + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPA.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN SHOP_PRODUCT SPT "
                + "  ON SA0.SUPPLYPRODUCTID = SPT.ID AND SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM " + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " LEFT JOIN AREAINFO AI " + "  ON SA0.AREAID = AI.AREAID " + " LEFT JOIN BUYERINFO BI "
                + "  ON SA0.CUSTOMID = BI.BUYERID AND BI.DELFLAG <> '0' " + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' " + " LEFT JOIN USERS UR "
                + "  ON SPA.APPROVEUSER = UR.USERID " +

                " REPLACEHERE " + // 用于替换

                " WHERE SPA.DEL_FLAG = '1' AND SA0.SALETYPE <> '1' ";
        // 产品审核(天音定向)的场合
        if (Type.equals("01")) {
            sql = sql.replaceAll("REPLACEHERE", ",ncproductmain np,relation_productnc rp");
            sql += " and np.invcode = rp.invcode  and rp.productid = PM.productid ";

            sql += "and SA0.SALETYPE  in (3,4)";
        }// 产品审核(天音非定向)
        else if (Type.equals("")) {
            sql = sql.replaceAll("REPLACEHERE", ",ncproductmain np,relation_productnc rp");
            sql += " and np.invcode = rp.invcode and rp.productid = PM.productid ";
            sql += " and SA0.SALETYPE  in (2)";
        }

        // 产品审核(供应商)的场合
        else {
            sql = sql.replaceAll("REPLACEHERE", "");
        }

        // 超级管理员放开区域限制
        if (!UsersCategory.Administrator.equals(UsersCategory.valueOf(Integer.parseInt(loVo.getCategoryId())))) {

            sql += " AND EXISTS (SELECT RSF.AREAID FROM " + " USERSSTAFFRELATION US "
                    + " INNER JOIN USERSTAFFINFO UF ON US.STAFFID = UF.STAFFID"
                    + " INNER JOIN RELATION_STAFFMANAGEAREA RSF ON UF.STAFFID = RSF.STAFFID" + " WHERE US.USERID='"
                    + loVo.getLogin_id() + "' AND   nvl(AI.Areaid, BI.areaid)=RSF.AREAID) ";

        }

        if (condition.getSupplyId() != null && !"".equalsIgnoreCase(condition.getSupplyId())) {
            if ("tyAll".equalsIgnoreCase(condition.getSupplyId())) {
                sql += " AND SPL.SUPPLY_TYPE = '01' ";
            } else {
                sql += " AND SPT.SUPPLYID=" + condition.getSupplyId() + "";
            }
        } else {
            sql += " AND (SPL.SUPPLY_TYPE NOT IN ('01','02','03') OR SPL.SUPPLY_TYPE IS NULL) ";
        }
        // if (condition.getApproveStatus() != null
        // && !"".equalsIgnoreCase(condition.getApproveStatus()))
        if (!"".equals(StringHelperTools.nvl(condition.getApproveStatus()))) {
            if ("1".equals(condition.getApproveStatus())) {
                // 未审核
                sql += " AND SPA.APPROVESTATUS='1' ";
            } else {
                if ("2".equals(condition.getApproveStatus())) {
                    // 二审中
                    sql += " AND (SPA.APPROVESTATUS='4') ";

                } else if ("3".equals(condition.getApproveStatus())) {
                    // 已审核
                    sql += " AND (SPA.APPROVESTATUS='3' OR SPA.APPROVESTATUS='2') ";
                }
            }
        }
        if (!"".equals(StringHelperTools.nvl(condition.getProductName())))
        // if (condition.getProductName() != null
        // && !"".equalsIgnoreCase(condition.getProductName()))
        {
            sql += " AND lower(replace(PM.PRODUCTNAME,' ','') ) like '%"
                    + condition.getProductName().replaceAll("\\s*", "").toLowerCase() + "%'";
        }

        sql += " ORDER BY SPA.EXPAPPROVETIME DESC,SPT.SUPPLYID,SPT.PRODUCTID ";

        String execSql = "";
        execSql = Pager.getPageDatas(pageVo, sql);

        return getJdbcTemplate().query(execSql, new ProductApproveVoMapper());
    }

    /**
     * 获取厂商供应商
     * 
     * @return
     */
    /*@Override
    public List<SupplyVo> getFirmSupplyName() {

        String sql = "SELECT ID,USER_ID,NAME FROM SUPPLY WHERE DEL_FLAG='1' AND (SUPPLY_TYPE = '02' OR SUPPLY_TYPE = '03') ORDER BY NAME";
        List<SupplyVo> list = getJdbcTemplate().query(sql, new SupplyVoMapper());
        return list;
    }*/

    @Override
    public List<ProductApproveVo> GetByPage4Firm(ConditionApproveVo condition, PageVo pageVo) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        sb.append(" SPA.ID ");
        sb.append(",SPA.SUPPLYAREAID ");
        sb.append(",PM.PRODUCTNAME ");
        sb.append(",SA0.AREAID ");
        sb.append(",AI.AREANAME ");
        sb.append(",SPA.PRICEBEFORE ");
        sb.append(",SPA.PRICEAFTER ");
        sb.append(",SPA.STOCKBEFORE ");
        sb.append(",SPA.STOCKAFTER ");
        sb.append(",SPA.REMARK ");
        sb.append(",SPA.EXPAPPROVETIME ");
        sb.append(",SPA.APPROVETIME ");
        sb.append(",UR.USERNAME ");
        sb.append(",SPT.SUPPLYID ");
        sb.append(",SPL.NAME AS SUPPLYNAME ");
        sb.append(",SPA.APPROVESTATUS ");
        sb.append(",SPA.SALESTATUS ");
        sb.append(",SA0.ISCONTINUOUS ");
        sb.append(",SA0.STATUS ");
        sb.append(",SA0.SALETYPE ");
        sb.append(",SA0.SUPPLYPRODUCTID AS SHOPPRODUCTID ");
        sb.append(",NVL(BI.COMPANYNAME, BI.SHOPNAME) AS COMPANYNAME ");
        sb.append(",SPL.SUPPLY_TYPE ");
        sb.append(" FROM ");
        sb.append("  SHOP_PRODUCT_APPROVE SPA ");
        sb.append(" INNER JOIN SUPPLY_AREA0 SA0 ");
        sb.append("  ON SPA.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' ");
        sb.append(" INNER JOIN SHOP_PRODUCT SPT ");
        sb.append("  ON SA0.SUPPLYPRODUCTID = SPT.ID AND SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' ");
        sb.append(" INNER JOIN PRODUCT_MAIN PM ");
        sb.append("  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' ");
        sb.append(" LEFT JOIN AREAINFO AI ");
        sb.append("  ON SA0.AREAID = AI.AREAID ");
        sb.append(" LEFT JOIN BUYERINFO BI ");
        sb.append("  ON SA0.CUSTOMID = BI.BUYERID AND BI.DELFLAG <> '0' ");
        sb.append(" INNER JOIN SUPPLY SPL ");
        sb.append("  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' ");
        sb.append(" LEFT JOIN USERS UR ");
        sb.append("  ON SPA.APPROVEUSER = UR.USERID ");
        sb.append(" WHERE SPA.DEL_FLAG = '1' AND SA0.SALETYPE <> '1' AND (SPL.SUPPLY_TYPE = '02' or SPL.SUPPLY_TYPE = '03')");

        if (condition.getSupplyId() != null && !"".equalsIgnoreCase(condition.getSupplyId())) {
            sb.append(" AND SPL.id = " + condition.getSupplyId());
        }
        if (condition.getApproveStatus() != null && !"".equalsIgnoreCase(condition.getApproveStatus())) {
            if ("1".equals(condition.getApproveStatus())) {
                // 未审核
                sb.append(" AND SPA.APPROVESTATUS='1' ");
            } else {
                // 已审核
                sb.append(" AND (SPA.APPROVESTATUS='2' OR SPA.APPROVESTATUS='3') ");
            }
        }
        if (condition.getProductName() != null && !"".equalsIgnoreCase(condition.getProductName())) {
            sb.append(" AND lower(replace(PM.PRODUCTNAME,' ','') ) like '%"
                    + condition.getProductName().replaceAll("\\s*", "").toLowerCase() + "%'");
        }

        sb.append(" ORDER BY SPA.EXPAPPROVETIME DESC,SPT.SUPPLYID,SPT.PRODUCTID ");

        String execSql = "";
        execSql = Pager.getPageDatas(pageVo, sb.toString());

        return getJdbcTemplate().query(execSql, new ProductApproveVoMapper());
    }

    @Override
    public int GetTotalCount4Firm(ConditionApproveVo condition) {
        String sql = "SELECT COUNT(1) "
                + " FROM "
                + "  SHOP_PRODUCT_APPROVE SPA "
                + " INNER JOIN SUPPLY_AREA0 SA0 "
                + "  ON SPA.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01' "
                + " INNER JOIN SHOP_PRODUCT SPT "
                + "  ON SA0.SUPPLYPRODUCTID = SPT.ID AND SPT.DEL_FLAG = '1' AND SPT.ISVALID = '01' "
                + " INNER JOIN PRODUCT_MAIN PM "
                + "  ON SPT.PRODUCTID = PM.PRODUCTID AND PM.DEL_FLAG = '1' "
                + " LEFT JOIN AREAINFO AI "
                + "  ON SA0.AREAID = AI.AREAID "
                + " LEFT JOIN BUYERINFO BI "
                + "  ON SA0.CUSTOMID = BI.BUYERID AND BI.DELFLAG <> '0' "
                + " INNER JOIN SUPPLY SPL "
                + "  ON SPT.SUPPLYID = SPL.ID AND SPL.DEL_FLAG = '1' "
                + " LEFT JOIN USERS UR "
                + "  ON SPA.APPROVEUSER = UR.USERID "
                + " WHERE SPA.DEL_FLAG = '1' AND SA0.SALETYPE <> '1' AND (SPL.SUPPLY_TYPE = '02' or SPL.SUPPLY_TYPE = '03')";
        if (condition.getSupplyId() != null && !"".equalsIgnoreCase(condition.getSupplyId())) {
            sql += " AND SPL.id = " + condition.getSupplyId();
        }
        if (condition.getApproveStatus() != null && !"".equalsIgnoreCase(condition.getApproveStatus())) {
            if ("1".equals(condition.getApproveStatus())) {
                // 未审核
                sql += " AND SPA.APPROVESTATUS='1' ";
            } else {
                // 已审核
                sql += " AND (SPA.APPROVESTATUS='2' OR SPA.APPROVESTATUS='3') ";
            }
        }
        if (condition.getProductName() != null && !"".equalsIgnoreCase(condition.getProductName())) {
            sql += " AND lower(replace(PM.PRODUCTNAME,' ','') ) like '%"
                    + condition.getProductName().replaceAll("\\s*", "").toLowerCase() + "%'";
        }
        return getJdbcTemplate().queryForInt(sql);
    }

    /**
     * 根据非定向合并后的id取得该非定向供应商产品所有审核信息
     * 
     * @param approveId
     * @return
     */
    @Override
    public List<ProductApproveVo> GetApproveListById(String approveId) {
        String sql = " select spa.id from shop_product_approve spa"
                + " inner join supply_area0 SA0 ON spa.SUPPLYAREAID = SA0.ID AND SA0.DEL_FLAG = '1' AND SA0.ISVALID = '01'"
                + " inner join (select * from shop_product_approve spa1 "
                + " inner join supply_area0 SA01 ON spa1.SUPPLYAREAID = SA01.ID AND SA01.DEL_FLAG = '1' AND SA01.ISVALID = '01' where spa1.id="
                + approveId + ") t" + " on SA0.Supplyproductid = t.Supplyproductid"
                + " where SA0.Saletype='2' and SPA.DEL_FLAG = '1' AND SA0.SALETYPE <> '1' AND SPA.APPROVESTATUS='1'";

        return getJdbcTemplate().query(sql, new ProductApproveVoMapper1());
    }

    private static final class ProductApproveVoMapper1 implements RowMapper<ProductApproveVo> {

        public ProductApproveVo mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductApproveVo productApproveVo = new ProductApproveVo();
            productApproveVo.setId(rs.getString("ID"));
            return productApproveVo;
        }
    }

    /**
     * 供应商发布中的产品数
     * 
     * @param condition
     * @return
     */
    @Override
    public int publichNumBySupply(BigDecimal supplyId) {
        String sql = " select count(sp.id) from shop_product sp"
                + " left join supply_area0 sa on sa.supplyproductid=sp.id and sa.del_flag=1 and sa.isvalid='01' "
                + " left join supply_area1 sa1 on sa1.supplyproductid=sp.id and sa1.del_flag=1 and sa1.isvalid='01' "
                + " where 1=1 " + " and sp.supplyid=" + supplyId + " and sp.del_flag=1 and sp.isvalid='01' "
                + " and ( (sa.status = 1 and sa.saleStatus <> '1')"
                + " or (sa1.status = 1 and sa1.saleStatus <> '1' ))";
        return getJdbcTemplate().queryForInt(sql);
    }

    /**
     * 取得SHOP_PRODUCT_APPROVE中的id
     * 
     * @param condition
     * @author 王震
     * @date 2011-2-18 上午9:13:41
     * @return
     */
    public BigDecimal findApproveId() {
        String strsql = "select SEQ_SHOP_PRODUCT_APPROVE_PK.NEXTVAL from dual";
        return getJdbcTemplate().queryForObject(strsql, new RowMapper<BigDecimal>() {
            public BigDecimal mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getBigDecimal(1);
            }
        });
    }

    @Override
    public List<ProductApproveVo> getSupplyAreaId(String shopProductIds) {
        String condition = "";
        String[] ids = shopProductIds.split(",");
        for (String id : ids) {
            if (!"".equals(condition)) {
                condition += ",";
            }
            condition += id;
        }
        String sql = "select spa.id as shopProductApproveId from supply_area0 sa0 "
                + " inner join shop_product_approve spa on spa.supplyareaid = sa0.id and spa.del_flag = '1' and spa.approvestatus = '1'"
                + " where sa0.supplyproductid in (" + condition + ") "
                + " and sa0.del_flag = '1' AND sa0.ISVALID = '01' and sa0.salestatus = '1'";
        return (List<ProductApproveVo>) getJdbcTemplate().query(sql, new Object[] {},
                new RowMapper<ProductApproveVo>() {
                    public ProductApproveVo mapRow(ResultSet rs, int i) throws SQLException {
                        ProductApproveVo vo = new ProductApproveVo();
                        AutoInjection.Rs2Vo(rs, vo, null);
                        return vo;
                    }
                });
    }
    
	@Override
	public int updateGiftsFapiao(String gifts,String isfapiao,String supplyAreaId) {
		// TODO Auto-generated method stub
		String sql="update supply_area0 sa set sa.iscontinuous='"+isfapiao + "',"+ " sa.gifts='"+gifts+"',"+"where sa.id="+supplyAreaId;
		
		return getJdbcTemplate().update(sql);
		
	}

	@Override
	public int updateProductCountry(ShopProductVO vo,String type,String supplyType) {
		int a=0;
		String istlproduct="";
		String isautoapprove="02";//自动指定
		if(supplyType.equals(TellingConstance.supply_type03)){//省平台
			istlproduct="0";
		}else if(supplyType.equals(TellingConstance.supply_type04)){//县平台
			istlproduct="1";
		}
		if("1".equals(type)){
				String shopProductTempId=getShopProductTmepId();
				String shopProductId=getShopProductId();
		String	sqlInsert = " INSERT INTO shop_product_tmp(id,productId,isvalid,del_flag,supplyId,salemode,status,shopproductid) VALUES(?,?,?,?,?,?,?,?)";
		 Object[] args = new Object[]{
				    shopProductTempId,
					vo.getProductId(),
					vo.getIsValid(),
					vo.getDelflag(),
					vo.getSupplyId(),
					vo.getSaleMode(),
					vo.getStatus(),
					shopProductId
					};
			args = StringHelperTools.nullToString(args);
			logger.debug("添加指定供应商模式产品:"+BaseUtil.logSQL(sqlInsert,args));
			int m  = getJdbcTemplate().update(sqlInsert,args);
			if(m>0){
				String	sql2 = "INSERT INTO shop_product(id,productId,isvalid,del_flag,supplyId,salemode,ISTLPRODUCT,approvetime,isautoapprove) VALUES(?,?,?,?,?,?,?,sysdate,?)";
				Object[] args1 = new Object[]{
						shopProductId,
						vo.getProductId(),
						vo.getIsValid(),
						vo.getDelflag(),
						vo.getSupplyId(),
						vo.getSaleMode(),
						istlproduct,
						isautoapprove
						};
				args1 = StringHelperTools.nullToString(args1);
				logger.debug("添加指定供应商模式产品:"+BaseUtil.logSQL(sql2,args1));
				a = getJdbcTemplate().update(sql2,args1);
			}
		}else if("0".equals(type)){
			String shopProductId=getShopProductId();
			String sql=" update shop_product_tmp spt set spt.status=? and spt.shopproductid=? where spt.productid=? and spt.supplyid=? ";
			Object[] args = new Object[]{
					vo.getStatus(),shopProductId,vo.getProductId(),vo.getSupplyId()
					};
			int m  = getJdbcTemplate().update(sql,args);
			if(m>0){
				String	sql2 = "INSERT INTO shop_product(id,productId,isvalid,del_flag,supplyId,salemode,ISTLPRODUCT,approvetime,isautoapprove) VALUES(?,?,?,?,?,?,?,sysdate,?)";
				Object[] args1 = new Object[]{
						shopProductId,
						vo.getProductId(),
						vo.getIsValid(),
						vo.getDelflag(),
						vo.getSupplyId(),
						vo.getSaleMode(),
						istlproduct,
						isautoapprove
						};
				args1 = StringHelperTools.nullToString(args1);
				logger.debug("添加指定供应商模式产品:"+BaseUtil.logSQL(sql2,args1));
				a = getJdbcTemplate().update(sql2,args1);
			}
		}
		return a;
	}
	
	public String getShopProductTmepId(){
		String sqlId =" SELECT SEQ_SHOP_PRODUCT_TMP_KEY.NEXTVAL FROM dual ";
		Object[] params = {};
		String shopProId = "";
		List<String> ls = (List<String>) getJdbcTemplate().query(sqlId,params,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int i)throws SQLException {
					return rs.getString("NEXTVAL");
					}
				});
			if(ls.size()>0 && null!=ls){
				shopProId=ls.get(0);
		}
			return shopProId;
	}
	
	public String getShopProductId(){
		String sqlId =" SELECT SEQ_SHOP_PRODUCT_KEY.NEXTVAL FROM dual";
		Object[] params = {};
		String shopProId = "";
		List<String> ls = (List<String>) getJdbcTemplate().query(sqlId,params,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int i)throws SQLException {
					return rs.getString("NEXTVAL");
					}
				});
			if(ls.size()>0 && null!=ls){
				shopProId=ls.get(0);
		}
			return shopProId;
	}
	
	@Override
	public ShopProductVO queryProductTempCountry(ShopProductVO vo) {
		ShopProductVO spv=new ShopProductVO();
		StringBuffer sb= new StringBuffer();
		sb.append(" select spt.* from shop_product_tmp spt");
		sb.append(" where spt.supplyid=? and spt.productid=? and spt.del_flag='1' and spt.salemode='02' ");
		Object[] params =new  Object[]{vo.getSupplyId(),vo.getProductId()};
		List<ShopProductVO> ls = (List<ShopProductVO>) getJdbcTemplate().query(sb.toString(),params,
				new RowMapper<ShopProductVO>() {
					public ShopProductVO mapRow(ResultSet rs, int i)throws SQLException {
						ShopProductVO vo=new ShopProductVO();
						AutoInjection.Rs2Vo(rs, vo, null);
					    return vo;
					}
				});
		if(ls!=null&&ls.size()>0){
			spv=ls.get(0);
		}
		return spv;
	}

	@Override
	public String queryProductCountry(String id) {
		String str="";
		StringBuffer sb= new StringBuffer();
		sb.append(" select sp.productid from shop_product sp");
		sb.append(" where sp.id=? and sp.del_flag='1' ");
		Object[] params =new  Object[]{id};
		List<String> ls = (List<String>) getJdbcTemplate().query(sb.toString(),params,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int i)throws SQLException {
					    return rs.getString("productid");
					}
				});
		if(ls!=null&&ls.size()>0){
			str=ls.get(0);
		}
		return str;
	}

}
